package com.fyj.service;

import com.fyj.pojo.*;
import com.fyj.repository.LinkManageRepository;
import com.fyj.repository.MissionDetailRepository;
import com.fyj.repository.MyMissionRepository;
import com.fyj.repository.TypeRepository;
import com.fyj.utils.JdbcUtils;
import com.fyj.utils.ThreadUtil;
import org.apache.commons.lang3.RandomStringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * @author 钰杰
 * @className MissionService
 * @description VX:FengYJ996
 * @date 2019/12/411:27
 */
@Service
public class DbaMissionService {


    @Autowired
    private MyMissionRepository myMissionRepository;

    @Autowired
    private LinkManageRepository linkManageRepository;

    @Autowired
    private TypeRepository typeRepository;

    @Autowired
    private MissionDetailRepository missionDetailRepository;

    /**
     * 添加任务
     *
     * @param mission
     * @param userId
     */
    public boolean addMission(MyMission mission, int userId) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String format = simpleDateFormat.format(new Date());
        mission.setNumber(format);    //生成任务编号
        mission.setSubmitid(userId);    //任务提交者
        mission.setCdate(new Date());   //任务创建日期
        mission.setStatus(0);           //初始化任务状态：0 未审核
        MyMission newMyMission = myMissionRepository.save(mission);
        if (newMyMission.getLevel() != 0 && newMyMission.getLevel() > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 添加任务细节
     * 使用Redis存储 源数据库详情 以及 目标数据库详情
     *
     * @param dataSourceMap
     * @param missionDetail
     * @return
     */
    public String addMissionDetail(Map dataSourceMap, MissionDetail missionDetail, String dirver) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String format = simpleDateFormat.format(new Date());
        String random = RandomStringUtils.randomAlphanumeric(1, 3);
        String realNumber = format + random;
        missionDetail.setDetailid(realNumber);    //生成细节编号
        missionDetail.setDirver(dirver);          //驱动类型
        missionDetail.setPassword((String) dataSourceMap.get("password"));
        missionDetail.setUrl((String) dataSourceMap.get("url"));
        missionDetail.setUsername((String) dataSourceMap.get("username"));
        missionDetailRepository.save(missionDetail);
        return realNumber;

    }

    /**
     * 获取指定表字段
     *
     * @param dataSourceMap 数据源
     * @param tableName     表名
     * @return
     */
    public ArrayList<String> getFiledAll(Map dataSourceMap, String tableName) {
        ArrayList<String> filedsList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection(dataSourceMap);
            preparedStatement = connection.prepareStatement("SELECT COLUMN_NAME FROM information_schema.columns " +
                    "WHERE TABLE_NAME = '" + tableName + "'");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                filedsList.add(resultSet.getString("COLUMN_NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(resultSet, preparedStatement, connection);
        }
        return filedsList;
    }

    /**
     * 获取指定数据库所有表
     *
     * @param dataSourceMap 数据源
     * @param databaseName  选取数据库
     * @return
     */
    public ArrayList<String> getTablesAll(Map dataSourceMap, String databaseName) {
        ArrayList<String> tablesList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection(dataSourceMap);
            preparedStatement = connection.prepareStatement("SHOW TABLES FROM " + databaseName);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                tablesList.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(resultSet, preparedStatement, connection);
        }
        return tablesList;
    }

    /**
     * 获取所有库
     *
     * @param dataSourceMap
     * @return
     */
    public ArrayList<String> getDataBaseAll(Map dataSourceMap) {
        ArrayList<String> DataBasesList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement show_databases = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection(dataSourceMap);
            show_databases = connection.prepareStatement("SHOW DATABASES");
            resultSet = show_databases.executeQuery();
            while (resultSet.next()) {
                DataBasesList.add(resultSet.getString("Database"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(resultSet, show_databases, connection);
        }
        return DataBasesList;
    }

    /**
     * 链接请求数据库
     *
     * @param linkManageId linkManageId 链接ID
     * @param typeid       typeid  数据库类型
     * @return
     */
    public Map<String, String> DataBaseConnection(int linkManageId, Integer typeid) {
        Map<String, String> dataSourceMap = new HashMap<String, String>();
        Type type = typeRepository.findById(typeid).get();
        LinkManage linkManage = linkManageRepository.findById(linkManageId).get();
        dataSourceMap.put("driver", type.getDriver());
        dataSourceMap.put("url", type.getUrlPrefix() + linkManage.getIpAddress() + ":" +
                linkManage.getPort() + "/" + type.getUrlSuffix());
        dataSourceMap.put("username", linkManage.getUsername());
        dataSourceMap.put("password", linkManage.getPassword());
        Connection connection = JdbcUtils.getConnection(dataSourceMap);

        if (connection == null) {
            return null;
        }
        return dataSourceMap;
    }

    /**
     * 获取链接信息库
     *
     * @return
     */
    public List<LinkManage> getLinkManageAll() {
        return linkManageRepository.findAll();
    }

    /**
     * 获取所有任务信息
     *
     * @return
     */
    public List<MyMission> getAllMission() {
        return myMissionRepository.findAll();
    }

    /**
     * 扫描表结构
     *
     * @param missionNumber 任务编号
     * @return
     */
    public StringBuffer getSourceStructure(String missionNumber) {
        MyMission byNumber = myMissionRepository.findByNumber(missionNumber);   //获取任务信息
        MissionDetail bySourceInfo = missionDetailRepository.findByDetailid(byNumber.getSourceInfo());//获得源数据库信息
        /**
         * 当orcal时 url替换暂未处理
         */
        Map<String, String> sourceMap = this.exchangeDataSource(bySourceInfo);
        String filedName = bySourceInfo.getFiledName();
        String[] splitFiledName = filedName.split(",");         //分割所选字段
        try {
            StringBuffer createTable = JdbcUtils.scanningSourceDatabase(sourceMap, bySourceInfo.getDatabasesName(), bySourceInfo.getTableName(), splitFiledName);
            return createTable;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 目标库建表
     *
     * @param missionNumber
     * @param createTableSQL
     * @return
     */
    public int createTargetTable(String missionNumber, StringBuffer createTableSQL) {
        MyMission byNumber = myMissionRepository.findByNumber(missionNumber);   //获取任务信息
        MissionDetail byTarGetInfo = missionDetailRepository.findByDetailid(byNumber.getTarGetInfo());//获取目标数据库信息
        String targetTable = null;

        String databasesName = byTarGetInfo.getDatabasesName();
        String tableName = byTarGetInfo.getTableName();

        /**
         * 替换数据源
         * 当orcal时 url替换暂未处理
         */
        Map<String, String> createTargetMap = this.exchangeDataSource(byTarGetInfo);

        if ("".equals(byTarGetInfo.getTableName())) {//如果目标表名为空，则使用源数据库表名
            MissionDetail bySourceInfo = missionDetailRepository.findByDetailid(byNumber.getSourceInfo());//获得源数据库信息
            targetTable = bySourceInfo.getTableName();
            missionDetailRepository.flushTableName(byTarGetInfo.getDetailid(), targetTable);
        } else {
            //判断目标库是否已有重名表
            targetTable = tableName;
            ArrayList<String> tablesAll = this.getTablesAll(createTargetMap, databasesName);//获取表信息
            for (String tablesall : tablesAll) {
                if (targetTable.equals(tablesall)) {
                    return ResultCode.ERROR_DBA_NAMING_REPETITION_CODE;
                }
            }
        }

        try {
            JdbcUtils.createTable(createTargetMap, targetTable, createTableSQL);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ResultCode.SUCCESS_CODE;
    }

    /**
     * 实际数据库置换
     *
     * @param byDetailid
     * @return
     */
    public Map<String, String> exchangeDataSource(MissionDetail byDetailid) {
        Map<String, String> dataSourceMap = new HashMap<>();
        /**
         * 当orcal时 url替换暂未处理
         */
        String databasesName = byDetailid.getDatabasesName();
        String newUrl = byDetailid.getUrl().replace("information_schema", databasesName);
        dataSourceMap.put("driver", byDetailid.getDirver());
        dataSourceMap.put("url", newUrl);
        dataSourceMap.put("username", byDetailid.getUsername());
        dataSourceMap.put("password", byDetailid.getPassword());
        return dataSourceMap;
    }

    /**
     * 获取源数据
     *
     * @param missionNumber
     * @return
     */
    public List<String> getSourceData(String missionNumber) {
        MyMission byNumber = myMissionRepository.findByNumber(missionNumber);
        MissionDetail bySourceInfo = missionDetailRepository.findByDetailid(byNumber.getSourceInfo());//获取源数据库信息
        MissionDetail byTarGetInfo = missionDetailRepository.findByDetailid(byNumber.getTarGetInfo());//获取源数据库信息
        Map<String, String> dataSource = this.exchangeDataSource(bySourceInfo);
        try {
            List<String> insertSql = JdbcUtils.getSourceData(dataSource, bySourceInfo.getTableName(), byTarGetInfo.getTableName(), bySourceInfo.getFiledName());
            return insertSql;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 目标数据库添加数据
     *
     * @param missionNumber
     * @param insertSqlList
     * @param threadCount
     */
    public void insertTargetData(String missionNumber, List<String> insertSqlList, int threadCount) {
        MyMission byNumber = myMissionRepository.findByNumber(missionNumber);
        MissionDetail byTargetInfo = missionDetailRepository.findByDetailid(byNumber.getTarGetInfo());
        MissionDetail bySourceInfo = missionDetailRepository.findByDetailid(byNumber.getSourceInfo());
        Map<String, String> targetDataSource = this.exchangeDataSource(byTargetInfo);//目标数据库Map
        Map<String, String> sourceDataSource = this.exchangeDataSource(bySourceInfo);//源数据库Map
        try {
            long dataCount = JdbcUtils.getCountFromSourceData(sourceDataSource, bySourceInfo.getTableName());
            for (int i = 1; i < threadCount; i++) { //启动指定线程添加
                List<String> exList = ThreadUtil.AllocationThread(targetDataSource, dataCount, insertSqlList, threadCount);
                if (null != exList) {
                    Thread thread = new Thread(new ThreadUtil(targetDataSource, exList));
                    thread.start();
                } else {
                    return;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 数据转移
     *
     * @param missionNumber 任务编号
     * @param threadCount   线程数
     * @return
     */
    @Transactional    //失败回滚
    public int transferData(String missionNumber, int threadCount) {
        //扫描源数据库结构
        StringBuffer createTableSQL = this.getSourceStructure(missionNumber);
        //目标数据库创建表
        int createTable = this.createTargetTable(missionNumber, createTableSQL);
        if (5210 == createTable) {
            return createTable;
        }
        //获取源数据库信息
        List<String> sourceData = this.getSourceData(missionNumber);
        //目标数据库添加数据
        this.insertTargetData(missionNumber, sourceData, threadCount);
        return 0;
    }

    /**
     * 查看进度
     *
     * @param missionNumber
     * @return
     */
    public int checkWork(String missionNumber) {
        MyMission byNumber = myMissionRepository.findByNumber(missionNumber);
        MissionDetail bySourceid = missionDetailRepository.findByDetailid(byNumber.getSourceInfo());//源数据信息
        MissionDetail byTarGetInfo = missionDetailRepository.findByDetailid(byNumber.getTarGetInfo());//目标数据信息
        Map<String, String> sourceMap = exchangeDataSource(bySourceid);
        Map<String, String> targetMap = exchangeDataSource(byTarGetInfo);

        try {
            long countFromSourceData = JdbcUtils.getCountFromSourceData(sourceMap, bySourceid.getTableName());//总量
            int nowWork = 0;//已执行量
            long countFromTargetData = JdbcUtils.getCountFromSourceData(targetMap, byTarGetInfo.getTableName());
            nowWork = (int) (countFromTargetData / countFromSourceData * 100);

            if (nowWork != countFromSourceData) {
                return nowWork;

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

}
